import xlwings as xw
import pandas as pd
import os
from env import ROOT_PATH

def copy_to_template(source_sheet, tocopy_loc, target_sheet, topaste_loc):

  start_cell = source_sheet.range(tocopy_loc)
  # Dynamically identify the last row and column of the table
  # Check if the table only has one value
  if start_cell.value is None:
      # No table to copy
      print(f"No data found in source starting cell {tocopy_loc}.")
  else:
      # Find the last row by extending down until an empty cell is found
      last_row = start_cell.end('down').row if start_cell.end('down').value is not None else start_cell.row
      # Find the last column by extending right until an empty cell is found
      last_column = start_cell.end('right').column if start_cell.end('right').value is not None else start_cell.column

  # Define the range of the table to be copied
  # Adjust the column reference as necessary
  source_range = source_sheet.range(tocopy_loc, (last_row, last_column))

  # Copy the data from the source range
  data_to_copy = source_range.value

  # If data_to_copy is not a list of lists (single value), make it one for consistency
  if not isinstance(data_to_copy, list) or not isinstance(data_to_copy[0], list):
      data_to_copy = [[data_to_copy]]

  # Paste the data into the target sheet starting from B5
  # Calculate the ending cell based on the size of the data_to_copy
  target_start_cell = target_sheet.range(topaste_loc)
  rows, cols = len(data_to_copy), len(data_to_copy[0])
  target_end_cell = target_start_cell.offset(rows-1, cols-1)

  # Define the target range and set its value
  target_range = target_sheet.range(target_start_cell, target_end_cell)
  target_range.value = data_to_copy
   

def combine_forms(file_path, output_file_path):

  app = xw.App(visible=False, add_book=False)
  app.display_alerts = False    # 关闭一些提示信息，可以加快运行速度。 默认为 True。
  app.screen_updating = True    # 更新显示工作表的内容。默认为 True。关闭它也可以提升运行速度。

  # Open the workbook with xlwings
  wb = app.books.open(file_path)      # 打开现有的工作簿

  wb0 = app.books.open(os.path.join(ROOT_PATH,'模版raw.xlsx'))      # 打开现有的工作簿

  loc_info = [['汇总表0','C3','B5'],['汇总表1','C3','B13'],['汇总表2','C3','B36'],
              ['汇总表3_1','C3','B44'],['汇总表3_2','C3','B47'], 
              ['汇总表4_1','C3','B56'],['汇总表4_2','C3','B62'],
              ['汇总表5','C3','B71'],['汇总表6','C3','B75']] #
  # Access the source and target sheets
  
  target_sht = wb0.sheets['Sheet1']
  for i in range(len(loc_info)):
    source_sht = wb.sheets[loc_info[i][0]]
    tocopy = loc_info[i][1]
    topaste = loc_info[i][2]
    copy_to_template(source_sht, tocopy, target_sht, topaste)

  target_sht.range('b51').value = '注：与上年同比='+target_sht.range('b49').value[:4]+'年案均/'+target_sht.range('b50').value[:4]+'年案均'
  target_sht.range('b52').value = '与上月环比='+target_sht.range('b48').value.strftime('%Y-%m')[-2:]+'月案均/'+target_sht.range('b47').value.strftime('%Y-%m')[-2:]+'月案均'
  target_sht.range('b67').value = '注：增长比例='+target_sht.range('b49').value[:4]+'年案均/'+target_sht.range('b50').value[:4]+'年案均'


  wb0.save(output_file_path)
  wb0.close()

  wb.close()

if __name__ == '__main__':
  res_path = os.path.join(ROOT_PATH,'results/2024-02-02\\')
  combine_forms(res_path+'summary.xlsx',res_path+'final.xlsx')